package com.ugoodtech.mdcc.web.controller;

import com.mysema.query.BooleanBuilder;
import com.ugoodtech.mdcc.core.Constants;
import com.ugoodtech.mdcc.core.LogConstants;
import com.ugoodtech.mdcc.core.domain.*;
import com.ugoodtech.mdcc.core.repository.*;
import com.ugoodtech.mdcc.web.dto.JsonResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RestController
@RequestMapping("/importStation")
public class ImportStationInfoController extends BaseController {

    @Autowired
    private AuditReportRepository auditReportRepository;

    @Autowired
    private StationRepository stationRepository;

    @Autowired
    private AuditColorReportRepository auditColorReportRepository;

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private UserLogRepository userLogRepository;

    private static final Logger logger = LoggerFactory.getLogger(ImportStationInfoController.class);


    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    /**
     * 判断Excel的版本,获取Workbook
     *
     * @param in
     * @return
     * @throws IOException
     */
    private Workbook getWorkbok(InputStream in, File file) throws IOException {
        Workbook wb = null;
        if (file.getName().endsWith(EXCEL_XLS)) {
            //Excel 2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith(EXCEL_XLSX)) {
            // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    /**
     * 判断文件是否是excel
     *
     * @throws Exception
     */
    private JsonResponse checkExcelVaild(File file) {
        if (!file.exists()) {
            return JsonResponse.errorResponseWithError("抱歉", "文件不存在");
        }
        if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
            return JsonResponse.errorResponseWithError("抱歉", "文件不是Excel");
        }
        return JsonResponse.successResponse();
    }

    private static List<AuditColorReport> stationColorReportList = new ArrayList<>();

    @RequestMapping(value = "/info", method = RequestMethod.POST)
    public JsonResponse importStation(Long loginUserId, String fileName, HttpServletRequest request) throws Exception {
        isUserLogin();
        User user = userRepository.findOne(loginUserId);
        if (null == user) {
            return JsonResponse.errorResponseWithError("抱歉", "用户未登陆");
        }
        String templetPath = request.getSession().getServletContext().getRealPath("");

        // 同时支持Excel 2003、2007
        // 创建文件对象
//        File excelFile = new File("C:\\Users\\Administrator\\Desktop\\漆星客户资料导入模版.xlsx");
        File excelFile = new File(templetPath + "/import/" + fileName);
        // 文件流
        FileInputStream in = new FileInputStream(excelFile);
        if (!checkExcelVaild(excelFile).isSuccess()) {
            return null;
        }
        Workbook workbook = getWorkbok(in, excelFile);
        // 这种方式 Excel2003/2007/2010都是可以处理的
        //Workbook workbook = WorkbookFactory.create(is);

        //获取第一个sheet
        //为跳过第一行标题设置
        String value = "";
        //new出上传的题目格式
        AuditColorReport auditColorReport = new AuditColorReport();
        //答案ID
        Long answerId = 0L;
        Sheet sheet = workbook.getSheetAt(0);
        for (Row row : sheet) {
            if (row.getRowNum() == 0) {
                continue;
            }
            stationColorReportList = new ArrayList<>();
            //设置省份
            stationColorReportList = addList(stationColorReportList, row, 0, 173L);
            //设置城市
            stationColorReportList = addList(stationColorReportList, row, 1, 174L);
            //设置维修站名称
            stationColorReportList = addList(stationColorReportList, row, 2, 175L);
            //设置维修站地址
            stationColorReportList = addList(stationColorReportList, row, 3, 186L);
            //设置客户状态
            auditColorReport = new AuditColorReport();
            Cell cell = row.getCell(4);
            if (cell != null) {
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                value = row.getCell(4).toString();
            }
            auditColorReport.setQuestionId(184L);
            answerId = getAnswerId(value, 184L);
            if (answerId != null) {
                auditColorReport.setAnswerId(answerId.toString());
            } else {
                auditColorReport.setAnswerId("362");
                auditColorReport.setAnswerOther(value);
            }
            stationColorReportList.add(auditColorReport);
            //设置客户类型
            auditColorReport = new AuditColorReport();
            cell = row.getCell(5);
            if (cell != null) {
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                value = row.getCell(5).toString();
            } else {
                value = "";
            }
            auditColorReport.setQuestionId(185L);
            answerId = getAnswerId(value, 185L);
            if (answerId != null) {
                auditColorReport.setAnswerId(answerId.toString());
            } else {
                auditColorReport.setAnswerId("366");
                auditColorReport.setAnswerOther(value);
            }
            stationColorReportList.add(auditColorReport);
            //设置维修站联系人姓名
            stationColorReportList = addList(stationColorReportList, row, 6, 2L);
            //设置维修站编号
            stationColorReportList = addList(stationColorReportList, row, 7, 181L);
            //设置联系人职位
            stationColorReportList = addList(stationColorReportList, row, 8, 3L);
            //设置联系人手机
            stationColorReportList = addList(stationColorReportList, row, 9, 4L);
            //设置维修站类型
            auditColorReport = new AuditColorReport();
            cell = row.getCell(10);
            if (cell != null) {
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                value = row.getCell(10).toString();
            } else {
                value = "";
            }
            auditColorReport.setQuestionId(7L);
            answerId = getAnswerId(value, 7L);
            if (answerId != null) {
                auditColorReport.setAnswerId(answerId.toString());
                auditColorReport.setAnswerOther(value);
            }
            stationColorReportList.add(auditColorReport);
            //设置维修站具体品牌
            auditColorReport = new AuditColorReport();
            cell = row.getCell(11);
            if (cell != null) {
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                value = row.getCell(11).toString();
            } else {
                value = "";
            }
            auditColorReport.setQuestionId(8L);
            String[] answerArray = getAnswerArray(value);
            StringBuffer answerIds = new StringBuffer("");
            for (String answerValue : answerArray) {
                answerId = getAnswerId(answerValue, 8L);
                if (null != answerId) {
                    answerIds.append(answerId + ",");
                } else {
                    answerIds.append("369,");
                    auditColorReport.setAnswerOther(answerValue);
                }
            }
//            String subStr = subValue(answerIds.toString());
            auditColorReport.setAnswerId(answerIds.toString());
            stationColorReportList.add(auditColorReport);
            //设置维修站所属集团
            stationColorReportList = addList(stationColorReportList, row, 12, 9L);
            //设置维修站每月钣喷维修钣件数
            stationColorReportList = addList(stationColorReportList, row, 13, 10L);
            //设置每月钣喷维修台次
            stationColorReportList = addList(stationColorReportList, row, 14, 11L);
            //设置维修站油漆月用量
            stationColorReportList = addList(stationColorReportList, row, 15, 12L);
            //设置油漆品牌
            auditColorReport = new AuditColorReport();
            cell = row.getCell(16);
            if (cell != null) {
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                value = row.getCell(16).toString();
            } else {
                value = "";
            }
            answerId = getAnswerId(value, 13L);
            auditColorReport.setQuestionId(13L);
            if (answerId != null) {
                auditColorReport.setAnswerId(answerId.toString());
            } else {
                auditColorReport.setAnswerId("280");
                auditColorReport.setAnswerOther(value);
            }
            stationColorReportList.add(auditColorReport);
            //设置品牌定位
            auditColorReport = new AuditColorReport();
            cell = row.getCell(17);
            if (cell != null) {
                value = row.getCell(17).toString();
            } else {
                value = "";
            }
            answerArray = getAnswerArray(value);
            auditColorReport.setQuestionId(182L);
            if (answerArray.length == 1) {
                answerId = getAnswerId(answerArray[0], 182L);
//                subStr = subValue(answerIds.toString());
                auditColorReport.setAnswerId(answerId.toString());
            } else if (answerArray.length == 2) {
                auditColorReport.setAnswerId("281,282");
            }
            stationColorReportList.add(auditColorReport);
            //设置油漆服务商
            stationColorReportList = addList(stationColorReportList, row, 18, 14L);
            //设置前处理人数
            stationColorReportList = addList(stationColorReportList, row, 19, 15L);
            //设置面漆人数
            stationColorReportList = addList(stationColorReportList, row, 20, 16L);
            //设置抛光人数
            stationColorReportList = addList(stationColorReportList, row, 21, 17L);
            //设置调漆人数
            stationColorReportList = addList(stationColorReportList, row, 22, 18L);
            //设置喷漆房数量
            stationColorReportList = addList(stationColorReportList, row, 23, 19L);
            //设置气压
            stationColorReportList = addList_chose(stationColorReportList, row, 24, 20L);
            //设置光亮度
            stationColorReportList = addList_chose(stationColorReportList, row, 25, 21L);
            //设置洁净度
            stationColorReportList = addList_chose(stationColorReportList, row, 26, 39L);
            //设置可否进行加温
            stationColorReportList = addList_chose(stationColorReportList, row, 27, 22L);
            //设置加温类型
            auditColorReport = new AuditColorReport();
            cell = row.getCell(28);
            if (cell != null) {
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                value = row.getCell(28).toString();
            } else {
                value = "";
            }
            answerArray = getAnswerArray(value);
            answerIds = new StringBuffer("");
            auditColorReport.setQuestionId(23L);
            for (String answerValue : answerArray) {
                answerId = getAnswerId(answerValue, 23L);
                if (null != answerId) {
                    answerIds.append(answerId + ",");
                } else {
                    auditColorReport.setAnswerOther(answerValue);
                }
            }
//            subStr = subValue(answerIds.toString());
            auditColorReport.setAnswerId(answerIds.toString());
            stationColorReportList.add(auditColorReport);
            //设置空压机类型
            stationColorReportList = addList_chose(stationColorReportList, row, 29, 24L);
            //设置是否安装干冷机
            stationColorReportList = addList_chose(stationColorReportList, row, 30, 25L);
            //设置压缩空气洁净度
            stationColorReportList = addList_chose(stationColorReportList, row, 31, 26L);
            //设置喷漆房内有没有油水分离器
            stationColorReportList = addList_chose(stationColorReportList, row, 32, 27L);
            //设置打磨设备类型
            stationColorReportList = addList_chose(stationColorReportList, row, 33, 28L);
            //设置打磨机数量(圆形)
            stationColorReportList = addList(stationColorReportList, row, 34, 29L);
            //设置打磨机数量(长方形)
            stationColorReportList = addList(stationColorReportList, row, 35, 30L);
            //设置打磨机质量
            stationColorReportList = addList_chose(stationColorReportList, row, 36, 32L);
            //设置打磨机吸尘能力
            stationColorReportList = addList_chose(stationColorReportList, row, 37, 33L);
            //设置该维修站红外线烤灯状况，配备数量（台）
            stationColorReportList = addList(stationColorReportList, row, 38, 34L);
            //设置喷枪类型-HVLP
            stationColorReportList = addList(stationColorReportList, row, 39, 35L);
            //设置喷枪类型-传统
            stationColorReportList = addList(stationColorReportList, row, 40, 36L);
            //设置喷枪类型-中涂
            stationColorReportList = addList(stationColorReportList, row, 41, 37L);
            //设置喷枪喷涂质量
            stationColorReportList = addList_chose(stationColorReportList, row, 42, 38L);
            //设置底漆品牌
            stationColorReportList = addList(stationColorReportList, row, 43, 40L);
            //设置底漆型号
            stationColorReportList = addList(stationColorReportList, row, 44, 41L);
            //原子灰品牌
            stationColorReportList = addList(stationColorReportList, row, 45, 42L);
            //原子灰型号
            stationColorReportList = addList(stationColorReportList, row, 46, 43L);
            //中涂底漆品牌
            stationColorReportList = addList(stationColorReportList, row, 47, 44L);
            //中涂底漆型号
            stationColorReportList = addList(stationColorReportList, row, 48, 45L);
            //底色漆品牌
            stationColorReportList = addList(stationColorReportList, row, 49, 46L);
            //底色漆型号
            stationColorReportList = addList(stationColorReportList, row, 50, 47L);
            //清漆品牌
            stationColorReportList = addList(stationColorReportList, row, 51, 48L);
            //清漆型号
            stationColorReportList = addList(stationColorReportList, row, 52, 49L);
            //中涂底漆前是否使用水磨（单选）
            stationColorReportList = addList_chose(stationColorReportList, row, 53, 50L);
            //颜色准确度
            stationColorReportList = addList_chose(stationColorReportList, row, 54, 51L);
            //最常用的颜色查询工具（单选）
            stationColorReportList = addList_chose(stationColorReportList, row, 55, 52L);
            //AkzoNobel服务商人员来店服务时使用的颜色工具 （多选）
            auditColorReport = new AuditColorReport();
            cell = row.getCell(56);
            if (cell != null) {
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                value = row.getCell(56).toString();
            } else {
                value = "";
            }
            auditColorReport.setQuestionId(53L);
            answerArray = getAnswerArray(value);
            answerIds = new StringBuffer("");
            for (String answerValue : answerArray) {
                answerId = getAnswerId(answerValue, 53L);
                if (null != answerId) {
                    answerIds.append(answerId + ",");
                }
            }
//            subStr = subValue(answerIds.toString());
            auditColorReport.setAnswerId(answerIds.toString());
            stationColorReportList.add(auditColorReport);
            //返工率最高的颜色（必须填写色号）
            stationColorReportList = addList(stationColorReportList, row, 57, 54L);
            //配方非常稳定的颜色（必须填写色号）
            stationColorReportList = addList(stationColorReportList, row, 58, 55L);
            //问题类型
            auditColorReport = new AuditColorReport();
            cell = row.getCell(59);
            if (cell != null) {
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                value = row.getCell(59).toString();
            } else {
                value = "";
            }
            auditColorReport.setQuestionId(56L);
            answerArray = getAnswerArray(value);
            answerIds = new StringBuffer("");
            for (String answerValue : answerArray) {
                answerId = getAnswerId(answerValue, 56L);
                if (null != answerId) {
                    answerIds.append(answerId + ",");
                } else {
                    answerIds.append("266,");
                    auditColorReport.setAnswerOther(answerValue);
                }
            }
//            subStr = subValue(answerIds.toString());
            auditColorReport.setAnswerId(answerIds.toString());
            stationColorReportList.add(auditColorReport);
            //具体说明
            stationColorReportList = addList(stationColorReportList, row, 60, 57L);
            //诉求类型
            auditColorReport = new AuditColorReport();
            cell = row.getCell(61);
            if (cell != null) {
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                value = row.getCell(61).toString();
            } else {
                value = "";
            }
            auditColorReport.setQuestionId(59L);
            answerArray = getAnswerArray(value);
            answerIds = new StringBuffer("");
            for (String answerValue : answerArray) {
                answerId = getAnswerId(answerValue, 59L);
                if (null != answerId) {
                    answerIds.append(answerId + ",");
                } else {
                    answerIds.append("272,");
                    auditColorReport.setAnswerOther(answerValue);
                }
            }
//            subStr = subValue(answerIds.toString());
            auditColorReport.setAnswerId(answerIds.toString());
            stationColorReportList.add(auditColorReport);
            //具体说明
            stationColorReportList = addList(stationColorReportList, row, 62, 60L);
            //todo 调用插入接口
            JsonResponse jsonResponse = toComplete(user, stationColorReportList,0);
            if (jsonResponse.isSuccess() == false) {
                return jsonResponse;
            }
        }
        userLogService.addUserLog(this.getCurrentUser(), null, LogConstants.operate_type_send_import, LogConstants.business_type_customer, null, null);
        return JsonResponse.successResponse();
    }

    @Autowired
    private QuestionAnswerOptionRepository questionAnswerOptionRepository;

    /**
     * 封装通用方法--填空题
     *
     * @param stationColorReportList
     * @param row
     * @param cellNum
     * @param questionId
     * @return
     */
    private List<AuditColorReport> addList(List<AuditColorReport> stationColorReportList, Row row, Integer cellNum, Long questionId) {
        AuditColorReport auditColorReport = new AuditColorReport();
        Cell cell = row.getCell(cellNum);
        String value = "";
        if (cell != null) {
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            value = row.getCell(cellNum).toString();
        }
        try {
            if (questionId.equals(4L)) {
                auditColorReport.setAnswerOther(value);
            } else {
                Long longValue = Long.valueOf(value);
                auditColorReport.setAnswerOther(longValue.toString());
            }
        } catch (Exception e) {
            auditColorReport.setAnswerOther(value);
        }
        auditColorReport.setQuestionId(questionId);
        stationColorReportList.add(auditColorReport);
        return stationColorReportList;
    }


    /**
     * 封装通用方法--选择题
     *
     * @param stationColorReportList
     * @param row
     * @param cellNum
     * @param questionId
     * @return
     */
    private List<AuditColorReport> addList_chose(List<AuditColorReport> stationColorReportList, Row row, Integer cellNum, Long questionId) {
        AuditColorReport auditColorReport = new AuditColorReport();
        String value = row.getCell(cellNum) == null ? "" : row.getCell(cellNum).toString();
        Long answerId = getAnswerId(value, questionId);
        auditColorReport.setQuestionId(questionId);
        if (answerId != null) {
            auditColorReport.setAnswerId(answerId.toString());
        }
        stationColorReportList.add(auditColorReport);
        return stationColorReportList;
    }

    /**
     * 通过questionId与值查询具体答案
     *
     * @param value
     * @param questionId
     * @return
     */
    private Long getAnswerId(String value, Long questionId) {
        QQuestionAnswerOption qQuestionAnswerOption = QQuestionAnswerOption.questionAnswerOption;
        BooleanBuilder builder = new BooleanBuilder();
        builder.and(qQuestionAnswerOption.deleted.eq(false));
        builder.and(qQuestionAnswerOption.enabled.eq(true));
        builder.and(qQuestionAnswerOption.name.like(value));
        builder.and(qQuestionAnswerOption.question.id.eq(questionId));
        QuestionAnswerOption answerOption = questionAnswerOptionRepository.findOne(builder);
        if (answerOption == null) {
            return null;
        }
        return answerOption.getId();
    }

    /**
     * 判断是以什么符号分割的
     *
     * @param value
     * @return
     */
    private String[] getAnswerArray(String value) {
        String[] answerArray1 = value.split(",");
        String[] answerArray2 = value.split("，");
        if (answerArray1.length > 0 && answerArray2.length <= 0) {
            return answerArray1;
        }
        if (answerArray1.length <= 0 && answerArray2.length > 0) {
            return answerArray2;
        }
        if (answerArray1.length == answerArray2.length) {
            return answerArray1;
        }
        return new String[0];
    }

    /**
     * 去除字符串最后一位
     *
     * @param value
     * @return
     */
    private String subValue(String value) {
        if (!StringUtils.isEmpty(value)) {
            Integer strLength = value.length();
            value = value.substring(0, strLength - 1);
        }
        return value;
    }


    private JsonResponse toComplete(User user, List<AuditColorReport> stationColorReports,Integer userType) {
        logger.info("<<<<<<<<<<<<<<<<<<设置报告状态为：待审核>>>>>>>>>>>>>>>>>");
        AuditReport auditReport = new AuditReport();
        auditReport.setUserType(userType);
        auditReport.setAuditing(Constants.AUDIT_APPLY);
        auditReport.setUser(user);
        auditReport.setCategory(Constants.TECHCOLOR_REPORT);
        auditReport.setSupplement(false);
        auditReport = auditReportRepository.save(auditReport);


        logger.info("<<<<<<<<<<<<<<<<<<创建报告答案>>>>>>>>>>>>>>>>>");
        UserLog userLog = null;
//        获取维修站编号=================================================
        String stationNum = "";
        String stationName = "";
        for (AuditColorReport stationColorReport : stationColorReports) {
            if (stationColorReport.getQuestionId().equals(181L)) {
                stationNum = stationColorReport.getAnswerOther();
            }

        }
        for (AuditColorReport stationColorReport : stationColorReports) {
            if (stationColorReport.getQuestionId().equals(175L)) {
                stationName = stationColorReport.getAnswerOther();
                Station station = stationRepository.selectByName_check(stationName);
                if (station != null) {
                    //删除创建过的报告
                    deleteAuditReport(auditReport);
                    auditReportRepository.delete(auditReport);
                    //todo 记录错误的信息 存储起来 等导入完毕之后返回
                    return JsonResponse.errorResponseWithError("抱歉", stationName + "：维修站已存在，请搜索");
                }
            }
            stationColorReport.setAuditReport(auditReport);
            auditColorReportRepository.save(stationColorReport);
        }
        auditReport.setReAudit(false);
        Date date = new Date();
        auditReport.setUpdateTime(date);
        auditReport.setUpdateTimeStr(dateToStr(date));
        auditReportRepository.save(auditReport);
        logger.info("<<<<<<<<<<<<<<<<<<更新完毕>>>>>>>>>>>>>>>>>");
        logger.info("***************************************************************");
        try {
            UserLog savedUserLog = userLogService.addUserLog(this.getCurrentUser(), stationNum, LogConstants.operate_type_send_import, LogConstants.business_type_customer, null, null);
            savedUserLog.setStationNo(stationNum);
            savedUserLog.setStationName(stationName);
            userLogRepository.save(savedUserLog);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return JsonResponse.successResponse();
    }

    private SimpleDateFormat simpleDateFormat = new SimpleDateFormat(Constants.DEFAULT_DATE_FORMAT);

    //将日期转换成字符串
    private String dateToStr(Date date) {
        return simpleDateFormat.format(date);
    }

    /**
     * 删除已经存在的报告答案
     */
    private void deleteAuditReport(AuditReport auditReport) {
        QAuditColorReport qAuditColorReport = QAuditColorReport.auditColorReport;
        BooleanBuilder builder = new BooleanBuilder();
        builder.and(qAuditColorReport.deleted.eq(false));
        builder.and(qAuditColorReport.enabled.eq(true));
        builder.and(qAuditColorReport.auditReport.id.eq(auditReport.getId()));
        List<AuditColorReport> auditColorReportList = (List<AuditColorReport>) auditColorReportRepository.findAll(builder);
        for (AuditColorReport report : auditColorReportList) {
            auditColorReportRepository.delete(report);
        }
    }

}
